#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_exam1.py
# author:王雯雯
# datetime:2024-11-12 
# software: PyCharm

import pymysql
from DBUtils.PooledDB import PooledDB
from..config.database_config import config
from..models.dept_model import Dept


pool = PooledDB(
    creator=pymysql,
    **config,
    maxconnections=10,
    mincached=2,
    blocking=True
)


# 根据院系号查询院系信息
def query_dept_by_dno(dno):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM dept WHERE Dno = %s"
        cursor.execute(sql, (dno,))
        result = cursor.fetchone()
        if result:
            dept_obj = Dept(*result)
            cursor.close()
            return dept_obj
        connection.close()
    except pymysql.Error as e:
        print(f"查询院系信息时出错: {e}")
    return None


# 插入院系信息
def insert_dept(dept_obj):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "INSERT INTO dept (Dno, Dname, Dean) VALUES (%s, %s, %s)"
        cursor.execute(sql, (dept_obj.dno, dept_obj.dname, dept_obj.dean))
        connection.commit()
        cursor.close()
        connection.close()
        print("院系信息插入成功")
    except pymysql.Error as e:
        print(f"插入院系信息时出错: {e}")


# 根据院系号删除院系信息
def delete_dept_by_dno(dno):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "DELETE FROM dept WHERE Dno = %s"
        cursor.execute(sql, (dno,))
        connection.commit()
        cursor.close()
        connection.close()
        print("院系信息删除成功")
    except pymysql.Error as e:
        print(f"删除院系信息时出错: {e}")


# 根据院系名称查询院系信息（模糊查询）
def query_depts_by_name(dname):
    try:
        connection = pool.connection()
        cursor = connection.cursor()
        sql = "SELECT * FROM dept WHERE Dname LIKE %s"
        cursor.execute(sql, (f"%{dname}%",))
        results = cursor.fetchall()
        depts = []
        for result in results:
            dept_obj = Dept(*result)
            depts.append(dept_obj)
        cursor.close()
        connection.close()
        return depts
    except pymysql.Error as e:
        print(f"查询院系信息时出错: {e}")
    return []